/* 

   Create the ECTS database

 */

CREATE DATABASE [ECTS]
GO

/* 

   Create the Config table

 */

USE [ECTS]
GO

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[Config](
	[Instance] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PwdExpiration] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UACWorkflowApprovers] [nchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SCWorkflowApprovers] [nchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UserManagementGroup] [nchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SelfServicePwdResetEnabled] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ManagementURL] [nchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[eMailSource] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SMTPHost] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY];
GO

/* 

   Create the SiteProvision table

 */

CREATE TABLE [dbo].[SiteProvision](
 [RequestNum] [int] IDENTITY(1,1) NOT NULL,
 [SiteURL] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [SiteTitle] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [SiteDescription] [nchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Requester] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [RequestTimestamp] [smalldatetime] NULL,
 [Justification] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Approver] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [ApproveTimestamp] [smalldatetime] NULL,
 [Status] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Template] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SiteProvision] PRIMARY KEY CLUSTERED 
(
 [RequestNum] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/*

    Add Network service account
    
*/

USE [ECTS];

DECLARE @user nvarchar(75)
DECLARE @SQLStatement nvarchar(255)
SET @user = quotename(SUSER_SNAME(0x010100000000000514000000));  -- Get the local language name for SID S-1-5-20
SET @SQLStatement = N'CREATE USER [NetworkService] FOR LOGIN ' + @user;
EXEC sp_executesql @SQLStatement;
GO

EXEC sp_addrolemember 'db_datareader', 'NetworkService'
GO
EXEC sp_addrolemember 'db_datawriter', 'NetworkService'
GO

/* 

   Insert first row into Config table

 */

USE [ECTS];
INSERT INTO [dbo].[Config] (Instance) values ('Default');
GO
